package idv.arthur.Derby;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Test2 {

	public static void main(String[] args) throws SQLException {
		 DBHelper db = new DBHelper();   
	        db.createUserTable();   
	        db.insertUser(1,"zhangsan2","123");   
	        db.insertUser(1,"zhangsan3","1233");   
	        db.insertUser(1,"zhangsan4","1234");   
	        db.insertUser(1,"zhangsan5","12345");   
	        ResultSet rs = db.query();   
	        while(rs.next()){   
	            int id = rs.getInt("id");   
	            String name = rs.getString("name");   
	            String pwd = rs.getString("password");   
	            System.out.println("id->" + id + "\tname->" + name + "\tpassword->" + pwd);   
	        }   
	        rs.close();   

	}
}

class DBHelper
{      
    private Connection con = null ;         //连接   
  
    public DBHelper(){   
        //loadDriver("org.apache.derby.jdbc.EmbeddedDriver");     //load driver jdk6可以省略加载驱动这一步
        getConnection();                                        //init Connection   
           
    }   
       
  
    //load driver class   
    private void loadDriver(String driveName){   
        try{   
            Class.forName(driveName);   
        }catch(Exception e){   
            e.printStackTrace();   
            System.out.println("load driver failure");   
        }   
    }   
  
       
    //get Connection   
    private void getConnection(){   
        try{   
            //con = DriverManager.getConnection("jdbc:derby:userDB;create=true;user=test;password=test");    //userDB创建在class运行目录
            //con = DriverManager.getConnection("jdbc:derby:a/b/userDB;create=true;user=test;password=test");    //userDB创建在class下面的a/b文件夹下面，
            con = DriverManager.getConnection("jdbc:derby:d:/a/b/userDB;create=true;user=test;password=test");    //userDB创建在d盘下面的a/b文件夹下面，

        }catch(Exception e){   
            e.printStackTrace();   
        }   
    }   
       
    //create simple table for test   
    public void createUserTable(){   
        try{   
            Statement stat = con.createStatement();   
            String sql_create = "create table tb_user(id int,name varchar(20),password varchar(20))";   
            stat.execute(sql_create);   
            stat.close();   
        }catch(Exception e){   
            e.printStackTrace();   
        }   
           
    }   
       
    //insert user info   
    public void insertUser(int id,String name,String pwd){   
        String sql_insert = "insert into tb_user values (?,?,?)";   
        try{   
            PreparedStatement pst = con.prepareStatement(sql_insert);   
            pst.setInt(1,id);   
            pst.setString(2,name);   
            pst.setString(3,pwd);   
            int i = pst.executeUpdate();   
        }catch(Exception e){   
            e.printStackTrace();   
        }   
    }   
       
    //query user info   
    public ResultSet query(){   
        String sql_insert = "select * from tb_user";   
        try{   
            PreparedStatement pst = con.prepareStatement(sql_insert);   
            return pst.executeQuery();   
        }catch(Exception e){   
            e.printStackTrace();   
        }   
        return null;   
    }   
} 
